Alter PROC SP_SAVE_LOTS_CORRECTION
 ( 	@LOT_ID INT,
	@LOT_NUMBER VARCHAR(50),
	@STATUS VARCHAR(50),
	@PROCESS_TYPE VARCHAR(50),
	@PROCESS_STEP_ID INT,
 	@LOCATION_ID INT,
	@QUANTITY_IN INT,
	@QUANTITY_OUT INT,
	@DATE_IN CHAR(10),
	@DATE_OUT CHAR(10),
	@DATE_CODE VARCHAR(50),
	@WAFER_QTY_IN VARCHAR(50),
	@WAFER_QTY_OUT VARCHAR(50),
	@FAB_LOT_NUMBER VARCHAR(50),
	@VENDOR_LOT_NUMBER VARCHAR(50),
	@LOT_CATEGORY VARCHAR(50),
	@LOT_COMMENTS VARCHAR(1000),
	@WAFER_IDS VARCHAR(100),
	@IS_HOLD INT )
 AS
 BEGIN
	DECLARE @HOLD_STATUS INT
	DECLARE @LATEST_RECORD_TIMESTAMP DATETIME
	DECLARE @LOT_ENTERED_BY BIT

	DECLARE @DATE_TIME 	DATETIME
	DECLARE @START_DATE 	DATETIME
	DECLARE @END_DATE 	DATETIME

 	IF( @DATE_IN = '' )
	BEGIN
 		SET @DATE_IN = NULL
	END
	IF( @DATE_OUT = '' )
	BEGIN
  		SET @DATE_OUT = NULL
	END
	SET @HOLD_STATUS = 1
	IF( @IS_HOLD = 0 )
	BEGIN
		SET @HOLD_STATUS = 0 
	END
	IF @PROCESS_TYPE = 'TAPE'
	BEGIN
		SET @PROCESS_TYPE = 'TAPE & REEL'
	END

	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

	IF( @@ROWCOUNT = 0 )
	BEGIN
 		RETURN
	END

	IF @LOT_ID IS NOT NULL 
	BEGIN 
		DECLARE @TEMP_VARCHAR VARCHAR(20)
	
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 12:00AM'
		SET @START_DATE   = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 11:59PM' 
		SET @END_DATE     = CONVERT(DATETIME, @TEMP_VARCHAR)		
		
	/* UPDATING INTO LOT HISTORY TABLE STARTED HERE*/
		SELECT @DATE_TIME = (LOT_TABLE_HISTORY.TIMESTAMP) FROM LOT_TABLE_HISTORY WHERE LOT_ID = @LOT_ID
		IF @DATE_TIME BETWEEN @START_DATE AND @END_DATE
		BEGIN
			UPDATE LOT_TABLE_HISTORY SET DATE_IN = @DATE_IN, DATE_OUT = @DATE_OUT, LOT_COMMENTS = @LOT_COMMENTS, LOT_DATE_CODE = @DATE_CODE, LOT_MANUAL_HOLD=@HOLD_STATUS WHERE LOT_ID = @LOT_ID
		END
		ELSE
		BEGIN	
			INSERT INTO LOT_TABLE_HISTORY(LOT_ID, PROCESSSTEP_ID, LOCATION_ID,LOT_NUMBER,FAB_LOT_NUMBER,VENDOR_LOT_NUMBER,LOT_ENTERED_BY,LOT_MANUAL_HOLD,LOT_CATEGORY,WAFER_QTY_IN,WAFER_QTY_OUT,QTY_IN,QTY_OUT,DATE_IN,DATE_OUT,STAGE_NAME,STAGE_POSITION, LOT_CURRENT_STATUS, LOT_COMMENTS, TIMESTAMP, PO_NUMBER, SHOP_ORDER_NUMBER)
				(SELECT LOT_ID, PROCESSSTEP_ID, LOCATION_ID,LOT_NUMBER,FAB_LOT_NUMBER,VENDOR_LOT_NUMBER,LOT_ENTERED_BY,LOT_MANUAL_HOLD,LOT_CATEGORY,WAFER_QTY_IN,WAFER_QTY_OUT,QTY_IN,QTY_OUT,DATE_IN,DATE_OUT,STAGE_NAME,STAGE_POSITION,LOT_CURRENT_STATUS,LOT_COMMENTS,CURRENT_TIMESTAMP, PO_NUMBER, SHOP_ORDER_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID)
		END
	/*ENDED HERE*/
  
		IF( @PROCESS_TYPE = 'Fab' OR @PROCESS_TYPE='Fab Option')
		BEGIN
			IF ( @status='Close' ) 
			BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, WAFER_QTY_IN = @WAFER_QTY_IN,  DATE_IN=@DATE_IN, WAFER_QTY_OUT=@WAFER_QTY_OUT, DATE_OUT=@DATE_OUT, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  WAFER_QTY_IN = @WAFER_QTY_IN,  DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			UPDATE FAB_LOT SET WAFER_ID=@WAFER_IDS, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
		END
		IF(@PROCESS_TYPE='Wafer Store' )
		BEGIN
			IF ( @status='Close' )
			BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, WAFER_QTY_IN = @WAFER_QTY_IN,  DATE_IN=@DATE_IN, WAFER_QTY_OUT=@WAFER_QTY_OUT, DATE_OUT=@DATE_OUT,  VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  WAFER_QTY_IN = @WAFER_QTY_IN, DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			UPDATE INVENTORY_LOT SET TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
		END	
		ELSE IF( @PROCESS_TYPE = 'Sort' )
		BEGIN
			IF ( @status='Close' )
			BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, WAFER_QTY_IN = @WAFER_QTY_IN,  DATE_IN=@DATE_IN, WAFER_QTY_OUT=@WAFER_QTY_OUT, DATE_OUT=@DATE_OUT, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  WAFER_QTY_IN = @WAFER_QTY_IN,  DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			UPDATE SORT_LOT SET SL_WAFER_ID=@WAFER_IDS, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
		END
		IF( @PROCESS_TYPE='TAPE & REEL' )
		BEGIN
			IF ( @status='Close' )
			BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, QTY_OUT=@QUANTITY_OUT, DATE_OUT=@DATE_OUT,  VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  QTY_IN = @QUANTITY_IN, DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			--UPDATE INVENTORY_LOT SET TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
		END	
		
		ELSE IF( @PROCESS_TYPE = 'Assembly'  )
		BEGIN
			IF ( @status='Close' )
			BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, QTY_OUT=@QUANTITY_OUT, DATE_OUT=@DATE_OUT, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER ,FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			UPDATE ASSEMBLY_LOT SET TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
		END		

		ELSE IF( @PROCESS_TYPE = 'FG' OR @PROCESS_TYPE = 'Finished Goods' )
		BEGIN
			
			IF ( @STATUS= 'Close')
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			UPDATE INVENTORY_LOT SET IL_Qty_Balance = @QUANTITY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
		END
		ELSE 
		BEGIN
			
			IF ( @STATUS= 'Close')
			BEGIN
		 		UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, QTY_OUT=@QUANTITY_OUT, DATE_OUT=@DATE_OUT, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER, FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = @LOT_NUMBER, LOT_DATE_CODE = @DATE_CODE, LOT_COMMENTS = @LOT_COMMENTS, LOT_MANUAL_HOLD = @HOLD_STATUS,  QTY_IN = @QUANTITY_IN,  DATE_IN=@DATE_IN, VENDOR_LOT_NUMBER= @VENDOR_LOT_NUMBER ,FAB_LOT_NUMBER= @FAB_LOT_NUMBER, LOT_CATEGORY=@LOT_CATEGORY WHERE LOT_ID = @LOT_ID
			END
		END
	 END

    END	  



